MySQL 索引的使用~
索引的分类
MySQL 索引的五种类型:主键索引、唯索引、普通索引和全文索引。通过给字段添以提高数据的读取速度，提高项目的并发能力和抗压能力。
1、主键索引（Primary key）：主键是一种唯一性索引，但它必须指定为 PRIMARY KEY，每个表只能有一个主键。（这种是数据库自动创建的）
2、唯一索引（Unique key）：索引列的所有值都只能出现一次，即必须唯一，值可以为空。
3、普通索引（Key / Index）：手动通过 index，key 关键字来设置，它是基本的索引类型，值可以为空，没有唯一性的限制。
4、全文索引（FullText）：全文索引的索引类型为 FULLTEXT。全文索引可以在 varchar、char、 text 类型的列上创建，在特定的数据库下才有
5、组合索引：多列值组成一个索引，专门用于组合搜索
索引的使用
虽然索引可以加快查询速度，但是还是要记住下面几个原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
- 索引默认的数据接构是 B 树(BTree)
-- 显示所有的索引信息
show index from 表名
-- 创建职工表
create table worker_tb(
worker_id nchar(3),
warehouse_id nchar(4) not null ,
Email nchar(15) not null,
worker_salary int not null,
primary key(worker_id), -- 主键就不需要给其设置名称了，默认就是自己
unique key 唯一索引名 (warehouse_id),
key 索引名 (Email)
);
-- 或者创表时指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
-- 如果不想起别名直接 INDEX (username) 就行了
INDEX [indexName] (username)
);
-- 显示索引状况
show index from worker_tb;
可以看到，InnoDB默认使用的index_type是BTree
创建索引
创建索引有很多种方式：
-- 最基本的索引，它没有任何限制
CREATE INDEX indexName ON table_name (column_name)
-- 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
-- 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
-- 删除索引
DROP INDEX [indexName] ON mytable;
显示指定 BTREE 索引
-- ----------------------------
-- 用户表
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(64) NOT NULL COMMENT '密码，加密存储',
`phone` varchar(20) DEFAULT NULL COMMENT '注册手机号',
`email` varchar(50) DEFAULT NULL COMMENT '注册邮箱',
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`) USING BTREE, -- USING BTREE 使用 B树索引
UNIQUE KEY `phone` (`phone`) USING BTREE,
UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARSET = utf8
ROW_FORMAT = DYNAMIC COMMENT ='用户表';
建索引的几大原则
最左前缀匹配原则
使用联合索引进行查询时一定要遵循左前缀原则，什么是左前缀原则呢？就是说想让索引生效的话，一定要添加上第一个索引，只使用第二个索引进行查询的话会导致索引失效
-- 例如这里使用 (`id`,`title`) 做联合索引
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
比如上面创建的联合索引，假如我们的查询条件是 where id = '1' 或者 where title = '唐诗' 索引都会不失效
且 mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配
比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。
= 和 in 可以乱序
比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。
选择区分度高的列作为索引
区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10条记录。
索引列不能参与计算
索引列不能参与计算，保持列 “干净”，比如 from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp('2014-05-29')。
修改优先拓展
尽量的扩展索引，不要新建索引
比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
使用 SQL 函数生成随机数据
下面是一个具体的例子：
-- 创建表用户表
CREATE TABLE IF NOT EXISTS `testUser_tb`
(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL,
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`tel` CHAR(11) NOT NULL
) ENGINE = INNODB
DEFAULT CHARSET = UTF8;
-- 这里直接使用函数随机插入数据用来测试
-- 创建一个函数
DELIMITER $$ -- 写函数前必须要写，类似于标志符
-- 定义一个函数用来创建十万条数据
create function mock_data()
RETURNS INT -- 返回类型
BEGIN
-- 函数体
DECLARE num INT DEFAULT 1000000; -- 定义一个变量为十万
DECLARE i INT DEFAULT 0;
while i < num
DO
insert into testUser_tb (username, age, tel)
-- 插入数据
-- RAND()随机生成1-0之间的小数，跟上后面的生成随机的电话号码
-- FLOOR()因为上面的那个操作会产生小数，所以向下取整
values (concat('张三', i),
FLOOR(RAND() * 100),
FLOOR(concat('131', RAND() * ((99999999) - 10000000) + 10000000)));
set i = i + 1;
end while;
RETURN i;
END $$;
-- 执行函数
select mock_data();
对上面创建的随机数据创建索引
-- 创建一个索引
-- 就是在内存里创建一个B+树(默认情况)
create index username_index on testUser_tb(tel);
-- 查询一下发现相较于创建前明显节省了很多时间
select *
from testUser_tb
where tel like '131520%'; -- 不要使用左模糊
联合索引
什么是联合索引
联合索引, 也叫复合索引,说白了就是多个字段一起组合成一个索引
像下面这样使用 id + title 组合在一起构成一个联合索引
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
如果我们像上图那样创建了索引，我们只要保证我们的 id + title 两者结合起来全局唯一就 ok
建立联合索引同样是需要进行排序的，排序的规则就是按照联合索引所有列组成的字符串的之间的先后顺序进行排序，如 a 比 b 优先
如下：
CREATE TABLE IF NOT EXISTS trade_appointment_date
(
activity_date_id char(36) comment '可预约的日期ID主键' primary key,
activity_date datetime not null comment '可预约的日期',
activity_id char(36) not null comment '预约活动ID主键',
created_on timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
modified_on timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',
INDEX idx_activity_id_and_activity_date (activity_id, activity_date)
) comment '预约活动日期表' ENGINE = InnoDB
DEFAULT charset = utf8mb4;
一般联合索引的命名就是两个字段加一个 idx
联合索引的分组&排序
还是使用这个例子：
CREATE TABLE `text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
demo1: 当我们像下面这样写 sql 时，就会先按照 id 进行排序，当 id 相同时，再按照 title 进行排序
select * form text order by id, title;
demo2: 当我们像下面这样写 sql 时, 就会先将 id 相同的划分为一组，再将 title 相同的划分为一组
select id,title form text group by id, title;
demo3: ASC 和 DESC 混用，其实大家都知道底层使用B+树，本身就是有序的，要是不加限制的话，默认就是ASC，反而是 混着使用就使得索引失效
select * form text order by id ASC, title DESC;
什么场景下索引会失效？
索引在某些场景下可能会失效，导致查询性能下降或无法利用索引进行优化。以下是一些常见的情况，可能导致索引失效：
不使用索引列：如果查询条件中没有包含索引列，或者查询中使用的函数、表达式或类型转换导致无法使用索引进行匹配，那么索引将无法被利用。
数据列上的函数：如果查询中对数据列应用了函数，例如
LOWER、UPPER或SUBSTRING等，那么索引可能无法被使用。因为函数的应用会使索引无法直接匹配查询条件。数据列的类型转换：如果查询中对数据列进行了类型转换，例如将字符串转换为数字或将日期格式化，那么索引可能无法被使用。因为类型转换会导致无法直接匹配索引中的数据类型。
LIKE 查询的模式以通配符开头：如果使用 LIKE 查询，并且查询模式以通配符（例如
%）开头，那么索引可能无法被使用。因为通配符开头的模式需要进行全表扫描而不是利用索引。使用 OR 连接的条件：如果查询中使用了多个条件，并通过 OR 连接它们，那么索引可能无法被使用。因为 OR 连接的条件可能无法通过单个索引进行匹配，导致无法利用索引。
数据表过小：如果数据表的大小非常小，例如只有几行数据，那么数据库优化器可能认为全表扫描比使用索引更快速，并决定不使用索引。
索引选择性低：如果索引的选择性非常低，即索引中的不同值较少，那么数据库优化器可能认为全表扫描比使用索引更高效，并决定不使用索引。
需要注意的是，索引失效并不意味着索引完全无效或无用。在某些情况下，即使索引无法用于特定查询，它仍然可以对其他查询提供性能提升。索引的使用和失效取决于具体的查询条件、数据分布、数据库优化器的决策等多个因素。对于具体的查询性能问题，建议分析查询语句、数据分布和索引设计，以确定索引是否失效以及如何优化查询。